package cj.web.portal.dao;
/*
 *  
 *  
*/
import java.util.List;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.kly.domain.News;
import cj.web.kly.domain.SubjectNews;

/**
 * 
 * @author 
 *
 */
@DAO
public interface NewsVoDAO{
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,carouselImage,date(releaseAt) as releaseAt FROM News WHERE columnsId IN (##(:ids)) AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :2,:3 ")
    List<News> queryHomeLis(@SQLParam("ids")String columnsId,int start,int length);
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,carouselImage,date(releaseAt) as releaseAt FROM News WHERE isTop=1 AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :1,:2 ")
    List<News> queryTop(int start,int length);
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,carouselImage,date(releaseAt) as releaseAt FROM News WHERE columnsId=:1 #if(:2 != '-1' and :2 != null){ AND source LIKE '%##(:2)%' } AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :3,:4 ")
    List<News> query(int columnsId,String source,int start,int length);
	
	@SQL("SELECT count(1) FROM News WHERE columnsId=:1 #if(:2 != '-1' and :2 != null){ AND source LIKE '%##(:2)%' } AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC ")
    int querySize(int columnsId,String source);
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,carouselImage,date(releaseAt) as releaseAt FROM News WHERE columnsId=:1 AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :2,:3 ")
    List<News> query(int columnsId,int start,int length);
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,carouselImage,date(releaseAt) as releaseAt FROM News WHERE columnsId=:1 AND id NOT IN (:2) AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :3,:4 ")
    List<News> query(int columnsId,int id,int start,int length);
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,carouselImage,date(releaseAt) as releaseAt,content FROM News WHERE columnsId IN (##(:ids)) AND deleteFlag=1 AND releaseState=1 LIMIT :2,:3" )
	List<News> queryBycolumnsIdForContent(@SQLParam("ids")String columnsId,int start,int length);
		
	@SQL("SELECT count(1) FROM News WHERE columnsId=:1 AND deleteFlag=1 AND releaseState=1 " )
    int querySize(int columnsId);
    
    @SQL("SELECT *,date(releaseAt) as releaseAt FROM News WHERE id=:1 ")
    News queryById(int id);
    
    @SQL("SELECT *,date(releaseAt) as releaseAt FROM SubjectNews WHERE id=:1 ")
    SubjectNews querySubjectNewsById(int id);
    
    @SQL("SELECT id,type,title,carouselImage,date(releaseAt) as releaseAt,subjectColumnsId FROM SubjectNews WHERE subjectColumnsId=:1 AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :2,:3 ")
    List<SubjectNews> querySubject(int subjectColumnsId,int start,int length);
		
	@SQL("SELECT count(1) FROM SubjectNews WHERE subjectColumnsId=:1 AND deleteFlag=1 AND releaseState=1 " )
    int querySubjectSize(int subjectColumnsId);
	
	@SQL("UPDATE News SET pvNum=pvNum+1,pvNumFinal=pvNumFinal+1,updateAt=now() WHERE id=:1 ")
	void addPV(int id);
	
	@SQL("UPDATE SubjectNews SET pvNum=pvNum+1,pvNumFinal=pvNumFinal+1,updateAt=now() WHERE id=:1 ")
	void addSubjectNewsPV(int id);
	
	@SQL("SELECT id,type,title,carouselImage,date(releaseAt) as releaseAt,content FROM News WHERE columnsId=:1 AND deleteFlag=1 AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :2,:3 ")
    List<News> queryForContent(int columnsId,int start,int length);
	
	@SQL("SELECT id,type,columnsId,subjectId,subjectColumnsId,title,date(releaseAt) as releaseAt,content FROM News WHERE id NOT IN (##(:ids)) AND deleteFlag=1 #if(:2 != null){ AND (title LIKE '%##(:2)%' or content LIKE '%##(:2)%') } AND releaseState=1 ORDER BY stickAt DESC,releaseAt DESC LIMIT :3,:4 ")
    List<News> querySearchList(@SQLParam("ids")String not_in_news_columnsid,String keywords,int start,int length);
		
	@SQL("SELECT count(1) FROM News WHERE id NOT IN (##(:ids)) AND deleteFlag=1 #if(:2 != null){ AND (title LIKE '%##(:2)%' or content LIKE '%##(:2)%') } AND releaseState=1 " )
    int querySearchListSize(@SQLParam("ids")String not_in_news_columnsid,String keywords);
}